﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text.RegularExpressions;

namespace CCPSIP
{
    public class OracleKitUser
    {
        private const string kOracleServer = "ISENDER.CCP.COM.TW";
        private const string kOracleUserId = "ISENDER";
        private static byte[] kOracleKey = { 0x61, 0x4B, 0x68, 0x4A, 0x4A, 0x46, 0x41, 0x32, 0x73, 0x57, 0x66, 0x45, 0x73, 0x51, 0x7A, 0x30, 0x5A, 0x6D, 0x35, 0x69, 0x77, 0x51, 0x69, 0x39, 0x67, 0x6A, 0x69, 0x56, 0x4E, 0x44 };

        private const string kIdHeaderPattern = "^[0-9A-Fa-f]{8}$";

        private static string GetConnStrMIS()
        {
            return String.Format(
              "Data Source={0};Persist Security Info=True;User ID={1};Password={2}",
              kOracleServer, kOracleUserId, System.Text.Encoding.UTF8.GetString(kOracleKey));
        }

        /// <summary>
        /// 取得員工姓名
        /// </summary>
        /// <param name="EmpID">員工編號</param>
        /// <returns>員工姓名</returns>
        public static string getEmpName(string EmpID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                //string selStr = "SELECT FULL_NAME FROM isender_person_all WHERE PPS_CODE=:PPS_CODE";
                string selStr = "SELECT NAME FROM PERSON_ALL_MVIEW WHERE PPS_CODE=:PPS_CODE";
                OracleCommand countCmd = new OracleCommand(selStr, connOracle);
                countCmd.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = EmpID;
                return Convert.ToString(countCmd.ExecuteScalar());
            }
            finally
            {
                connOracle.Close();
            }
        }



        #region 員工身分判斷 Functions


        /// <summary>
        /// 判斷是否為管理部Boss
        /// </summary>
        /// <param name="EmpID">員工編號</param>
        /// <returns>管理部為true，否則false</returns>
        public static bool isBoss(string EmpID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                //string countStr = "SELECT COUNT(*) FROM isender_person_all"
                string countStr = "SELECT COUNT(*) FROM PERSON_ALL_MVIEW"
                + " WHERE PPS_CODE=:PPS_CODE"
                  + "   AND DEPT_NO IN ('11A1A1','22A1A1')";
                OracleCommand countCmd = new OracleCommand(countStr, connOracle);
                countCmd.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = EmpID;
                int count = Convert.ToInt32(countCmd.ExecuteScalar());
                return (count > 0);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 判斷是否為台灣主管(副部長以上)
        /// </summary>
        /// <param name="EmpID">員工編號</param>
        /// <returns>台灣主管為true，否則false</returns>
        public static bool isTWManager(string EmpID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                //string countStr = "SELECT COUNT(*) FROM isender_person_all"
                string countStr = "SELECT COUNT(*) FROM PERSON_ALL_MVIEW"
                + " WHERE PPS_CODE=:PPS_CODE AND DUTY_CODE<='C3'"
                  + "   AND COMPANY IN ('1','2','3','5','7','E','R')";
                OracleCommand countCmd = new OracleCommand(countStr, connOracle);
                countCmd.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = EmpID;
                int count = Convert.ToInt32(countCmd.ExecuteScalar());
                return (count > 0);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 判斷是否有撥打外線權限(台灣副課長以上)
        /// </summary>
        /// <param name="EmpID">員工編號</param>
        /// <returns>有撥打外線權限為true，否則false</returns>
        public static bool canCallOut(string EmpID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                //string countStr = "SELECT COUNT(*) FROM isender_person_all"
                string countStr = "SELECT COUNT(*) FROM PERSON_ALL_MVIEW"
                + " WHERE PPS_CODE=:PPS_CODE AND DUTY_CODE<='CD'"
                  + "   AND COMPANY IN ('1','2','3','5','7','E','R')";
                OracleCommand countCmd = new OracleCommand(countStr, connOracle);
                countCmd.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = EmpID;
                int count = Convert.ToInt32(countCmd.ExecuteScalar());
                return (count > 0);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 判斷是否為採購人員(台灣)
        /// </summary>
        /// <param name="EmpID">員工編號</param>
        /// <returns>採購人員為true，否則false</returns>
        public static bool isPH(string EmpID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                //string countStr = "SELECT COUNT(*) FROM isender_person_all"
                string countStr = "SELECT COUNT(*) FROM PERSON_ALL_MVIEW"
                + " WHERE PPS_CODE=:PPS_CODE AND DUTY_CODE<='D1'"
                  + "   AND DEPT_NO IN ('11PHPH','22PHPH','R1PHPH')";
                OracleCommand countCmd = new OracleCommand(countStr, connOracle);
                countCmd.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = EmpID;
                int count = Convert.ToInt32(countCmd.ExecuteScalar());
                return (count > 0);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 判斷是否為業務人員(台灣)
        /// </summary>
        /// <param name="EmpID">員工編號</param>
        /// <returns>業務人員為true，否則false</returns>
        public static bool isSalesman(string EmpID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                //string countStr = "SELECT COUNT(*) FROM isender_person_all"
                string countStr = "SELECT COUNT(*) FROM PERSON_ALL_MVIEW"
                + " WHERE PPS_CODE=:PPS_CODE"
                  + "   AND SUBSTR(DEPT_NO,1,2) IN ('11','22','EE','R1')"
                  + "   AND SUBSTR(DEPT_NO,3,2) IN ('SL','IN')"
                  + "   AND (DUTY_CODE<='D1' OR DUTY_CODE IN ('G1'))";
                OracleCommand countCmd = new OracleCommand(countStr, connOracle);
                countCmd.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = EmpID;
                int count = Convert.ToInt32(countCmd.ExecuteScalar());
                return (count > 0);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 判斷是否為資訊中心人員
        /// </summary>
        /// <param name="EmpID">員工編號</param>
        /// <returns>資訊中心人員true，否則false</returns>
        public static bool isMIS(string EmpID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                //string countStr = "SELECT COUNT(*) FROM isender_person_all"
                string countStr = "SELECT COUNT(*) FROM PERSON_ALL_MVIEW"
                + " WHERE PPS_CODE=:PPS_CODE AND DUTY_CODE<='D1'"
                  + "   AND DEPT_NO IN ('11MIMI','22MIMI')";
                OracleCommand countCmd = new OracleCommand(countStr, connOracle);
                countCmd.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = EmpID;
                int count = Convert.ToInt32(countCmd.ExecuteScalar());
                return (count > 0);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 判斷是否為海外員工(副部長以下)
        /// </summary>
        /// <param name="EmpID">員工編號</param>
        /// <returns>海外員工true，否則false</returns>
        public static bool isAbroadEmp(string EmpID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                //string countStr = "SELECT COUNT(*) FROM isender_person_all"
                string countStr = "SELECT COUNT(*) FROM PERSON_ALL_MVIEW"
                + " WHERE PPS_CODE=:PPS_CODE AND DUTY_CODE>'C3'"
                  + "   AND COMPANY NOT IN ('1','2','3','5','7','8','E','R')";
                OracleCommand countCmd = new OracleCommand(countStr, connOracle);
                countCmd.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = EmpID;
                int count = Convert.ToInt32(countCmd.ExecuteScalar());
                return (count > 0);
            }
            finally
            {
                connOracle.Close();
            }
        }


        #endregion



        /// <summary>
        /// 取得使用者UserType
        /// </summary>
        /// <param name="CountryCode">手機門號國碼</param>
        /// <param name="MobileNumber">手機門號</param>
        /// <param name="UserName">使用者Custom Name或Full Name</param>
        /// <returns>有此使用者則回傳UserType，否則null</returns>
        public static string getUserType(string CountryCode, string MobileNumber, string UserName)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                //MySQL Code
                //string selStr = "SELECT CustomName, FullName, UserType FROM reg_user"
                //  + " WHERE Status='Y' AND CountryCode=:CountryCode AND MobileNum=:MobileNum"
                //  + " ORDER BY UserType DESC, CreateTime DESC, pid DESC";

                //2015/2/11 CHChou原汁原味改寫
                string selStr = "SELECT * FROM"
                                + " (SELECT CUSTOMNAME, FULLNAME, USERTYPE, CREATETIME, PID"
                                + " FROM REG_USER "
                                + " WHERE STATUS='Y' "
                                + " AND COUNTRYCODE=:CountryCode "
                                + " AND MOBILENUM=:MobileNum "
                                + " AND USERTYPE LIKE 'CCP%'"
                                + " ORDER BY CREATETIME DESC, PID DESC"
                                + " )"
                                + " UNION ALL"
                                + " SELECT * FROM"
                                + " (SELECT CUSTOMNAME, FULLNAME, USERTYPE, CREATETIME, PID"
                                + " FROM REG_USER "
                                + " WHERE STATUS='Y' "
                                + " AND COUNTRYCODE=:COUNTRYCODE "
                                + " AND MOBILENUM=:MOBILENUM "
                                + " AND USERTYPE NOT LIKE 'CCP%'"
                                + " ORDER BY CREATETIME DESC, PID DESC)";

                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("CountryCode", OracleType.VarChar).Value = CountryCode;
                selCmd.Parameters.Add("MobileNum", OracleType.VarChar).Value = MobileNumber;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    string CustomName = Convert.ToString(row["CustomName"]).ToUpper();
                    string FullName = Convert.ToString(row["FullName"]).ToUpper();
                    string inputName = UserName.ToUpper();

                    if (CustomName.Contains(inputName) || FullName.Contains(inputName))
                        return Convert.ToString(row["UserType"]);
                }
                return null;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得使用者UserType
        /// </summary>
        /// <param name="SIP_Num">SIP帳號</param>
        /// <returns>有此使用者則回傳UserType，否則null</returns>
        public static string getUserType(string SIP_Num)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT UserType FROM reg_user"
                  + " WHERE Status='Y' AND SIP_Num=:SIP_Num"
                  + " ORDER BY CreateTime DESC, pid DESC";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
                return Convert.ToString(selCmd.ExecuteScalar());
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得 SIP 帳號對應的 Device UUID
        /// </summary>
        /// <param name="SIP_Num">SIP帳號</param>
        /// <param name="connOracle">現有的OracleConnection</param>
        /// <returns>有此帳號則回傳Device UUID，否則空白</returns>
        public static string getUUID(string SIP_Num, OracleConnection connOracle)
        {
            string selStr = "SELECT UUID FROM reg_user"
              + " WHERE Status='Y' AND SIP_Num=:SIP_Num"
              + " ORDER BY CreateTime DESC, pid DESC";
            OracleCommand selCmd = new OracleCommand(selStr, connOracle);
            selCmd.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
            return Convert.ToString(selCmd.ExecuteScalar());
        }


        /// <summary>
        /// 取得 Push Token 對應的 Device UUID
        /// </summary>
        /// <param name="PushToken">Device Push Token</param>
        /// <param name="PushType">推播類型</param>
        /// <param name="connOracle">現有的OracleConnection</param>
        /// <returns>有此 Push Token 則回傳Device UUID，否則空白或null</returns>
        public static string getUUIDbyPushToken(string PushToken, string PushType, OracleConnection connOracle)
        {
            if (string.IsNullOrEmpty(PushToken))
                return null;

            string selStr = "SELECT UUID FROM device_token"
              + " WHERE Status='Y' AND DeviceToken=:DeviceToken AND PushType=:PushType"
              + " ORDER BY CreateTime DESC, pid DESC";
            OracleCommand selCmd = new OracleCommand(selStr, connOracle);
            selCmd.Parameters.Add("DeviceToken", OracleType.VarChar).Value = PushToken;
            selCmd.Parameters.Add("PushType", OracleType.VarChar).Value = PushType;
            return Convert.ToString(selCmd.ExecuteScalar());
        }


        /// <summary>
        /// 取得員工編號對應的 Device UUID
        /// </summary>
        /// <param name="PPS_CODE">員工編號</param>
        /// <param name="connOracle">現有的OracleConnection</param>
        /// <returns>有此員工編號則回傳Device UUID，否則空白或null</returns>
        public static string getUUIDbyEmpID(string PPS_CODE, OracleConnection connOracle)
        {
            if (string.IsNullOrEmpty(PPS_CODE))
                return null;

            string selStr = "SELECT UUID FROM reg_user"
              + " WHERE Status='Y' AND PPS_CODE=:PPS_CODE"
              + " ORDER BY CreateTime DESC, pid DESC";
            OracleCommand selCmd = new OracleCommand(selStr, connOracle);
            selCmd.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = PPS_CODE;
            return Convert.ToString(selCmd.ExecuteScalar());
        }


        /// <summary>
        /// 更新會員的Device Push Token
        /// </summary>
        /// <param name="DeviceUUID">Device UUID</param>
        /// <param name="DeviceToken">Device Push Token</param>
        /// <param name="PushType">推播類型</param>
        public static void UpdateDeviceToken(string DeviceUUID, string DeviceToken, string PushType)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string updStr = "UPDATE device_token"
                  + " SET Status='Y', UpdateTime=:DateTime, PushType=:PushType, DeviceToken=:DeviceToken"
                  + " WHERE UUID=:DeviceUUID";
                OracleCommand updCmd = new OracleCommand(updStr, connOracle);
                updCmd.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                updCmd.Parameters.Add("PushType", OracleType.VarChar).Value = PushType;
                updCmd.Parameters.Add("DeviceToken", OracleType.VarChar).Value = DeviceToken;
                updCmd.Parameters.Add("DeviceUUID", OracleType.VarChar).Value = DeviceUUID;
                updCmd.ExecuteNonQuery();
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 檢查有效會員中是否有此IdHeader
        /// </summary>
        /// <param name="idHeader">UUID 的開頭字串</param>
        /// <returns>有此IdHeader回傳true，否則false</returns>
        public static bool hasValidIdHeader(string idHeader)
        {
            if (!Regex.IsMatch(idHeader, kIdHeaderPattern))
                return false;

            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string countStr = "SELECT COUNT(*) FROM reg_user"
                  + " WHERE Status='Y' AND UUID LIKE :UUID";
                OracleCommand countCmd = new OracleCommand(countStr, connOracle);
                countCmd.Parameters.Add("UUID", OracleType.VarChar).Value = idHeader + "%";
                UInt32 count = Convert.ToUInt32(countCmd.ExecuteScalar());
                if (count > 0)
                    return true;
                else
                    return false;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得員工編號對應的帳號Array
        /// </summary>
        /// <param name="PPS_CODE">員工編號</param>
        /// <returns>員工編號對應的帳號Array</returns>
        public static string[] getUserIDbyEmpID(string PPS_CODE)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT SIP_Num FROM reg_user"
                  + " WHERE Status='Y' AND PPS_CODE=:PPS_CODE"
                  + " ORDER BY SIP_Num";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = PPS_CODE;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                List<string> userList = new List<string>();
                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    string SIP_Num = Convert.ToString(row["SIP_Num"]);
                    userList.Add(SIP_Num);
                }
                return userList.ToArray();
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得手機門號對應的帳號Array
        /// </summary>
        /// <param name="MobileNumber">User手機門號(含國碼)</param>
        /// <returns>手機門號對應的帳號Array</returns>
        public static string[] getUserIDbyMobile(string MobileNumber)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT SIP_Num FROM reg_user"
                  + " WHERE Status='Y' AND CONCAT(CountryCode, MobileNum)=:MobileNum"
                  + " ORDER BY UserType ASC, SIP_Num";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("MobileNum", OracleType.VarChar).Value = MobileNumber;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                List<string> userList = new List<string>();
                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    string SIP_Num = Convert.ToString(row["SIP_Num"]);
                    userList.Add(SIP_Num);
                }
                return userList.ToArray();
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 檢查有效會員中是否有此SIP帳號
        /// </summary>
        /// <param name="SIP_Num">SIP帳號</param>
        /// <returns>有此SIP帳號回傳true，否則false</returns>
        public static bool hasUser(string SIP_Num)
        {
            if (string.IsNullOrEmpty(SIP_Num))
                return false;
            else if (!Regex.IsMatch(SIP_Num, SipKit.kSIPNumPattern))
                return false;
            else
            {
                OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
                try
                {
                    connOracle.Open();
                    string selStr = "SELECT COUNT(*) FROM reg_user WHERE Status='Y' AND SIP_Num=:SIP_Num";
                    OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                    selCmd.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
                    int count = Convert.ToInt32(selCmd.ExecuteScalar());
                    return (count > 0);
                }
                finally
                {
                    connOracle.Close();
                }
            }
        }


        /// <summary>
        /// 修改自訂名稱
        /// </summary>
        /// <param name="userInfo">SipUserInfo物件</param>
        /// <param name="CustomName">User的自訂名稱</param>
        public static void UpdateCustomName(SipUserInfo userInfo, string CustomName)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            OracleTransaction myTrans = null;

            try
            {
                connOracle.Open();
                myTrans = connOracle.BeginTransaction();

                //【01】修改User自訂名稱
                //
                string updStr1 = "UPDATE reg_user SET CustomName=:CustomName"
                  + " WHERE Status='Y' AND SIP_Num=:SIP_Num";
                OracleCommand updCmd1 = new OracleCommand(updStr1, connOracle, myTrans);
                updCmd1.Parameters.Add("CustomName", OracleType.VarChar).Value = CustomName;
                updCmd1.Parameters.Add("SIP_Num", OracleType.VarChar).Value = userInfo.SIP_Num;
                updCmd1.ExecuteNonQuery();


                //【02】記錄修改Log
                //
                string insStr2 = "INSERT INTO log_user_change"
                  + " (LogTime, SIP_Num, FullName, ColumnName, OldValue, NewValue) VALUES"
                  + " (:DateTime, :SIP_Num, :FullName, 'CustomName', :OldValue, :NewValue)";
                OracleCommand insCmd2 = new OracleCommand(insStr2, connOracle, myTrans);
                insCmd2.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                insCmd2.Parameters.Add("SIP_Num", OracleType.VarChar).Value = userInfo.SIP_Num;
                insCmd2.Parameters.Add("FullName", OracleType.VarChar).Value = userInfo.FullName;
                insCmd2.Parameters.Add("OldValue", OracleType.VarChar).Value = userInfo.CustomName;
                insCmd2.Parameters.Add("NewValue", OracleType.VarChar).Value = CustomName;
                insCmd2.ExecuteNonQuery();


                //【99】Commit Transaction
                //
                myTrans.Commit();
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 檢查UUID + DeviceToken是否唯一
        /// </summary>
        /// <param name="DeviceUUID">裝置UUID</param>
        /// <param name="DeviceToken">Device Push Token</param>
        /// <param name="PushType">推播類型</param>
        /// <returns>UUID+DeviceToken唯一回傳true，否則false</returns>
        public static bool isUniqueToken(string DeviceUUID, string DeviceToken, string PushType)
        {
            if (string.IsNullOrEmpty(DeviceToken))
                return false;
            else if (DeviceToken.Length < 18)
                return false;

            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT COUNT(*) FROM device_token"
                  + " WHERE Status='Y' AND UUID=:UUID"
                  + "   AND DeviceToken=:DeviceToken AND PushType=:PushType";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("UUID", OracleType.VarChar).Value = DeviceUUID;
                selCmd.Parameters.Add("DeviceToken", OracleType.VarChar).Value = DeviceToken;
                selCmd.Parameters.Add("PushType", OracleType.VarChar).Value = PushType;
                int count = Convert.ToInt32(selCmd.ExecuteScalar());
                return (count == 1);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 更改裝置的UUID
        /// </summary>
        /// <param name="OldUUID">舊的UUID</param>
        /// <param name="NewUUID">新的UUID</param>
        /// <param name="DeviceToken">Device Push Token</param>
        /// <param name="PushType">推播類型</param>
        /// <param name="DeviceType">裝置類型</param>
        /// <returns>裝置的SIP帳號</returns>
        public static string UpdateDeviceUUID(string OldUUID, string NewUUID, string DeviceToken, string PushType, string DeviceType)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            OracleTransaction myTrans = null;

            try
            {
                connOracle.Open();

                //【01】抓取裝置的SIP帳號
                //
                string selStr3 = "SELECT SIP_Num FROM reg_user"
                  + " WHERE Status='Y' AND UUID=:UUID";
                OracleCommand selCmd3 = new OracleCommand(selStr3, connOracle);
                selCmd3.Parameters.Add("UUID", OracleType.VarChar).Value = OldUUID;
                string SIP_Num = Convert.ToString(selCmd3.ExecuteScalar());
                myTrans = connOracle.BeginTransaction();

                //【02】更新 Token Table 的UUID
                //
                string updStr1 = "UPDATE device_token SET UUID=:NewUUID"
                  + " WHERE Status='Y' AND UUID=:OldUUID"
                  + "   AND DeviceToken=:DeviceToken AND PushType=:PushType";
                OracleCommand updCmd1 = new OracleCommand(updStr1, connOracle, myTrans);
                updCmd1.Parameters.Add("NewUUID", OracleType.VarChar).Value = NewUUID;
                updCmd1.Parameters.Add("OldUUID", OracleType.VarChar).Value = OldUUID;
                updCmd1.Parameters.Add("DeviceToken", OracleType.VarChar).Value = DeviceToken;
                updCmd1.Parameters.Add("PushType", OracleType.VarChar).Value = PushType;
                int updateCount = updCmd1.ExecuteNonQuery();

                if (updateCount != 1)
                {
                    throw new Exception("Error_U1: Token is not unique");//不是唯一的Row
                }

                //【03】更新 User Table 的UUID
                //
                string updStr2 = "UPDATE reg_user SET UUID=:NewUUID"
                  + " WHERE Status='Y' AND UUID=:OldUUID";
                OracleCommand updCmd2 = new OracleCommand(updStr2, connOracle, myTrans);
                updCmd2.Parameters.Add("NewUUID", OracleType.VarChar).Value = NewUUID;
                updCmd2.Parameters.Add("OldUUID", OracleType.VarChar).Value = OldUUID;
                updCmd2.ExecuteNonQuery();

                //【04】記錄修改Log
                //
                string insStr4 = "INSERT INTO log_uuid_change"
                  + " (LogTime, SIP_Num, DeviceType, OldUUID, NewUUID, PushType, DeviceToken) VALUES"
                  + " (:DateTime, :SIP_Num, :DeviceType, :OldUUID, :NewUUID, :PushType, :DeviceToken)";
                OracleCommand insCmd4 = new OracleCommand(insStr4, connOracle, myTrans);
                insCmd4.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                insCmd4.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
                insCmd4.Parameters.Add("DeviceType", OracleType.VarChar).Value = DeviceType;
                insCmd4.Parameters.Add("OldUUID", OracleType.VarChar).Value = OldUUID;
                insCmd4.Parameters.Add("NewUUID", OracleType.VarChar).Value = NewUUID;
                insCmd4.Parameters.Add("PushType", OracleType.VarChar).Value = PushType;
                insCmd4.Parameters.Add("DeviceToken", OracleType.VarChar).Value = DeviceToken;
                insCmd4.ExecuteNonQuery();


                //【99】Commit Transaction
                //
                myTrans.Commit();

                return SIP_Num;
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 記錄個人圖片更新時間
        /// </summary>
        /// <param name="SIP_Num">SIP帳號</param>
        /// <param name="reqTime">圖片更新時間</param>
        /// <param name="iconSize">圖片檔案大小</param>
        public static void UpdateIconTime(string SIP_Num, DateTime reqTime, int iconSize)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string updStr1 = "UPDATE reg_user"
                  + " SET IconTime=:IconTime, IconSize=:IconSize"
                  + " WHERE Status='Y' AND SIP_Num=:SIP_Num";
                OracleCommand updCmd1 = new OracleCommand(updStr1, connOracle);
                updCmd1.Parameters.Add("IconTime", OracleType.DateTime).Value = reqTime;
                updCmd1.Parameters.Add("IconSize", OracleType.Int32).Value = iconSize;
                updCmd1.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
                updCmd1.ExecuteNonQuery();
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 記錄最新登入時間
        /// </summary>
        /// <param name="SIP_Num">SIP帳號</param>
        public static void UpdateLoginTime(string SIP_Num)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                string updStr1 = "UPDATE reg_user SET LastLogin=:DateTime"
                  + " WHERE Status='Y' AND SIP_Num=:SIP_Num";
                OracleCommand updCmd1 = new OracleCommand(updStr1, connOracle);
                updCmd1.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                updCmd1.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
                updCmd1.ExecuteNonQuery();
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得長春部門代碼
        /// </summary>
        /// <param name="EmpID">員工編號</param>
        /// <returns>有此員工編號回傳DEPT_NO，否則null</returns>
        public static string getDeptNo(string EmpID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                //string selStr = "SELECT DEPT_NO FROM isender_person_all WHERE PPS_CODE=:PPS_CODE"
                string selStr = "SELECT DEPT_NO FROM PERSON_ALL_MVIEW WHERE PPS_CODE=:PPS_CODE";
                    //+ " ORDER BY CREATE_TIME, pid";   //PERSON_ALL_MVIEW沒有pid, create_time
                OracleCommand countCmd = new OracleCommand(selStr, connOracle);
                countCmd.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = EmpID;
                return Convert.ToString(countCmd.ExecuteScalar());
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 檢查 User 是否可以註冊多個 SIP 帳號
        /// </summary>
        /// <param name="EmpID">員工編號</param>
        /// <param name="connOracle">現有的OracleConnection</param>
        /// <returns>可以有多個 SIP 帳號true，否則false</returns>
        public static bool canHaveMultiPhones(string EmpID, OracleConnection connOracle)
        {
            string countStr = "SELECT COUNT(*) FROM reg_user_sp"
              + " WHERE SpType='AllowMultiPhones' AND IDType='PPS_CODE' AND UserID=:UserID";
            OracleCommand countCmd = new OracleCommand(countStr, connOracle);
            countCmd.Parameters.Add("UserID", OracleType.VarChar).Value = EmpID;
            int count = Convert.ToInt32(countCmd.ExecuteScalar());
            return count > 0;
        }
    }
}